"""
author:mzy
date:2022/10/8
"""
import pymysql
import json
import pandas as pd
from get_path import BASE_DIR
from datetime import date,datetime


#连接数据库
conn = pymysql.connect(host="kbxzcron.rwlb.rds.aliyuncs.com",port=3306,user="qjzy_dev",passwd="Qjyz@1234",database="v2022dev",)

# #SQL语句
# sql = 'select mobile from fa_user where credit1 > 0; '
#
# #获取数据
# df = pd.read_sql(sql,conn)
#
# #转化为csv
# df.to_csv(path_or_buf="./data/test.csv",float_format=8,header=False,index=False)


#使用cursor()方法创建一个游标对象cursor
cursor = conn.cursor()


class ComplexEncoder(json.JSONEncoder):
    def default(self,obj):
        if isinstance(obj,datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(obj,date):
            return obj.strftime('%Y-%m-%d')
        else:
            return json.JSONEncoder.default(self,obj)


def get_result():
        # 需要执行的sql语句
        sql = "select mobile from fa_user where credit1 > 100000;"
        cursor.execute(sql)
        data = cursor.fetchall()
        print(data)
        # cols = cursor.description
        # res = format_data(cols,data)
        # res = format_data(data)
        cursor.close()
        conn.close()
# json.dumps(): 对数据进行编码，转成json格式
        data_json = json.dumps(data)
        print(data_json)
# 写入文档datajson
        with open(BASE_DIR + '/data/data.json', 'w')as file:
            file.write(data_json)
# get_result()
# 数据格式化 cols字段名，data结果集
# def format_data(cols, data):
# # def format_data(data):
#         # 字段数组 形式['id', 'name', 'password']
#             col = []  # 创建一个空列表以存放列名
#             for i in cols:
#                 col.append(i[0])
#             # 返回的数组集合 形式[{'id': 1, 'name': 'admin', 'password': '123456'}]
#             res = []
#             for iter in data:
#                 line_data = {}
#                 for index in range(0, len(col)):
#                     line_data[col[index]] = iter[index]
#                 res.append(line_data)
#             return res



